package org.jbit.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.jbit.bean.DictionaryHelp;
import org.jbit.bean.User;
import org.jbit.dao.BaseDao;

public class UserDao
{
	private Connection conn;

	private PreparedStatement ps;

	private ResultSet rs;
	 
	
	public List<User> getUser(String rows, String page, String sidx, String sord, String kw,
			String ci)
	{
		List<User> list=new ArrayList<User>();
		int rowsInt = Integer.valueOf(rows);
		int pageInt = Integer.valueOf(page);
		String sql=null;
		if (kw == null||kw.equals("null")){
			sql="select * from(select *,ROW_NUMBER() OVER(Order by "
				+sidx
				+" "
				+sord				
				+") rs from UserInfo)b where b.rs  BETWEEN "
				+((pageInt - 1) * rowsInt + 1)
				+" and "
				+pageInt * rowsInt;
		}
		else
		{
			sql="select * from(select *,ROW_NUMBER() OVER(Order by "
				+sidx
				+" "
				+sord				
				+") rs from UserInfo where "
				+ci
				+" like '%"
				+kw
				+"%')b where b.rs  BETWEEN "
				+((pageInt - 1) * rowsInt + 1)
				+" and "
				+pageInt * rowsInt;	

		}
try {
			
			conn = BaseDao.getConnection();
			ps = conn.prepareStatement(sql);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {		
				User Info=new User();
				Info.setID(rs.getString("ID").trim());
				Info.setUserName(rs.getString("userName").trim());
				Info.setRealName(rs.getString("realName").trim());
				Info.setUserMobile(rs.getString("userMobile"));
				Info.setPassword(rs.getString("password").trim());
				Info.setPersonId(rs.getString("personId").trim());
				Info.setRegitTime(rs.getString("regitTime").trim());
				Info.setType(rs.getString("type").trim());
				Info.setModuleMark(rs.getString("ModuleMark").trim());
				Info.setModuleMobile(rs.getString("ModuleMobile"));
				Info.setCarId(rs.getString("carId"));
				Info.setCountyId(rs.getString("countyId"));
				list.add(Info);
			}
		}

		
		catch (Exception e) {
			e.printStackTrace();
		}		
		finally {
			BaseDao.closeAll(rs, ps, conn);
		}
		return list;
	}
	
	
	public String getUserTotal(String kw,String ci)
	{
		String number="0";
		String sql=null;
		if (kw == null){
			sql="select count(*) number from(select *,ROW_NUMBER() OVER(Order by ID DESC) rs from UserInfo )b";	
		}
		else
		{
			sql="select count(*) number from(select *,ROW_NUMBER() OVER(Order by ID DESC) rs from UserInfo where "
				+ci
				+" like '%"
				+kw
				+"%')b";	

		}
try {
			
			conn = BaseDao.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			while (rs.next()) 
				number=rs.getString("number");
				
		}

		
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			BaseDao.closeAll(rs, ps, conn);
		}
		return number;
	}
	public boolean deleteUser(String ids) {
		// TODO Auto-generated method stub
		boolean flg=false;
		String sql = "delete from userInfo where ID=?";
	
		try {			
			conn = BaseDao.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1,ids);
			int i = ps.executeUpdate();

			if (i > 0) {
				flg = true;
			}
			      conn.close();
		}

		catch (Exception e) {
			e.printStackTrace();
		}

		finally {
			BaseDao.closeAll(rs, ps, conn);
		}
		return flg;
	}
	
	public boolean addUser(User user) {
		// TODO Auto-generated method stub
		boolean flg=false;
		String sql = "insert into userInfo (userName,realName,password,personId,regitTime,type,moduleMark,userMobile,ModuleMobile,carId,countyId)"
			+" values(?,?,?,?,?,?,?,?,?,?,?)";
	
		try {			
			conn = BaseDao.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1,user.getUserName());
			ps.setString(2,user.getRealName());
			ps.setString(3,user.getPassword());
			ps.setString(4,user.getPersonId());
			ps.setString(5,user.getRegitTime());
			ps.setString(6,user.getType());
			ps.setString(7,user.getModuleMark());
			ps.setString(8,user.getUserMobile());
			ps.setString(9,user.getModuleMobile());
			ps.setString(10,user.getCarId());
			ps.setString(11, user.getCountyId());
			int i = ps.executeUpdate();

			if (i > 0) {
				flg = true;
			}
			      conn.close();
		}

		catch (Exception e) {
			e.printStackTrace();
		}

		finally {
			BaseDao.closeAll(rs, ps, conn);
		}
		return flg;
	}
	
	public boolean updateUser(User user) {
		// TODO Auto-generated method stub
		boolean flg=false;
		String sql = "update  userInfo set userName=?,realName=?,password=?,personId=?,type=?,moduleMark=?,userMobile=?,ModuleMobile=?,carId=?,countyId=? where ID=?";
	
		try {			
			conn = BaseDao.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1,user.getUserName());
			ps.setString(2,user.getRealName());
			ps.setString(3,user.getPassword());
			ps.setString(4,user.getPersonId());			
			ps.setString(5,user.getType());
			ps.setString(6,user.getModuleMark());
			ps.setString(7,user.getUserMobile());
			ps.setString(8,user.getModuleMobile());
			ps.setString(9,user.getCarId());
			ps.setString(10, user.getCountyId());
			ps.setString(11,user.getID());
			
			int i = ps.executeUpdate();

			if (i > 0) {
				flg = true;
			}
			      conn.close();
		}

		catch (Exception e) {
			e.printStackTrace();
		}

		finally {
			BaseDao.closeAll(rs, ps, conn);
		}
		return flg;
	}
	public List<DictionaryHelp> getUserDictionary()
	{
		List<DictionaryHelp> list=new ArrayList<DictionaryHelp>();
		
		String sql=null;
		
			sql="select * from D_userSpecies";	

		
try {
			
			conn = BaseDao.getConnection();
			ps = conn.prepareStatement(sql);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				DictionaryHelp Info=new DictionaryHelp();
				Info.setKey(rs.getString("key"));
				Info.setValue(rs.getString("value"));
				list.add(Info);
			}
		}

		
		catch (Exception e) {
			e.printStackTrace();
		}		
		finally {
			BaseDao.closeAll(rs, ps, conn);
		}
		return list;
	}
	
	public String ModuleMarkByUserName(String UserName)
	{
		String ModuleMark="0";
		String sql=null;
		
			sql="select ModuleMark from userInfo where userName=?";	

try {

			conn = BaseDao.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, UserName);
			rs = ps.executeQuery();

			while (rs.next())
				ModuleMark = rs.getString("ModuleMark");
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			BaseDao.closeAll(rs, ps, conn);
		}
		return ModuleMark;
	}
	public User GetInfoByModuleMark(String ModuleMark)
	{
		User user=new User();
		String sql=null;		
			sql="select * from userInfo where ModuleMark=?";	

try {

			conn = BaseDao.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, ModuleMark);
			rs = ps.executeQuery();

			while (rs.next())
			{
				user.setID(rs.getString("ID"));
				user.setUserName(rs.getString("userName"));
				user.setRealName(rs.getString("realName"));
				user.setUserMobile(rs.getString("userMobile"));
				user.setPassword(rs.getString("password"));
				user.setPersonId(rs.getString("personId"));
				user.setRegitTime(rs.getString("regitTime"));
				user.setType(rs.getString("type"));
				user.setModuleMark(rs.getString("ModuleMark"));
				user.setModuleMobile(rs.getString("ModuleMobile"));	
				user.setCarId(rs.getString("carId"));
				user.setCountyId(rs.getString("countyId"));
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			BaseDao.closeAll(rs, ps, conn);
		}
		return user;
	}
	
	
	public List<User> getUser()
	{
		List<User> list=new ArrayList<User>();
		String sql=null;
		
			sql="select distinct * from UserInfo";	

	
try {
			
			conn = BaseDao.getConnection();
			ps = conn.prepareStatement(sql);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {		
				User Info=new User();
				Info.setID(rs.getString("ID").trim());
				Info.setUserName(rs.getString("userName").trim());
				Info.setRealName(rs.getString("realName").trim());
				Info.setUserMobile(rs.getString("userMobile"));
				Info.setPassword(rs.getString("password").trim());
				Info.setPersonId(rs.getString("personId").trim());
				Info.setRegitTime(rs.getString("regitTime").trim());
				Info.setType(rs.getString("type").trim());
				Info.setModuleMark(rs.getString("ModuleMark").trim());
				Info.setModuleMobile(rs.getString("ModuleMobile"));
				Info.setCarId(rs.getString("carId"));
				Info.setCountyId(rs.getString("countyId"));
				list.add(Info);
			}
		}

		
		catch (Exception e) {
			e.printStackTrace();
		}		
		finally {
			BaseDao.closeAll(rs, ps, conn);
		}
		return list;
	}
}

